package org.bigdata.framework.admin.web.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.bigdata.framework.admin.web.util.OpenOfficePdfConvert;
import org.bigdata.framework.core.model.BaikeCategory;
import org.bigdata.framework.core.model.BaikeTemplate;
import org.bigdata.framework.core.model.Classes;
import org.bigdata.framework.core.model.ShardingDataSource;
import org.bigdata.framework.core.model.ShardingRepositoryCount;
import org.bigdata.framework.core.model.ShardingRepositoryDataSource;
import org.bigdata.framework.core.model.ThesauriWord;
import org.bigdata.framework.core.model.ThesauriWordModule;
import org.bigdata.framework.core.model.ThesauriWordReference;
import org.bigdata.framework.core.model.ThesauriWordRelation;
import org.bigdata.framework.core.model.User;
import org.bigdata.framework.core.protocol.IBaseService;
import org.bigdata.framework.core.protocol.IDynamicService;
import org.bigdata.framework.core.protocol.IPublicService;
import org.bigdata.framework.core.protocol.IWordService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.xxl.conf.core.XxlConfClient;

@Controller
public class ShardingController extends BaseContoroller{
    @Autowired
    private IBaseService baseService;
    @Autowired
    private IPublicService publicService;
    @Autowired
    private IWordService iWordService;
    @Autowired
    private IDynamicService dynamicService;
    
    //系统管理---》分库分表配置
  	@RequestMapping("show-edit-sharding.html")
  	public String knowledgeEditSharding() {
  		return "/modules/sharding/knowledge_edit_sharding";
  	}
  	//分库分表数据源
  	@RequestMapping("knowledge-edit-sharding-database-list.html")
  	public String knowledgeEditShardingDatabaseList() {
  		return "/modules/sharding/knowledge_edit_sharding_database_list";
  	}
    // 分库分表新增修改页面
    @RequestMapping("knowledge-edit-sharding-database.html")
    public String knowledgeEditShardingDatabase() {
        return "/modules/sharding/knowledge_edit_sharding_database";
    }
    // 查看表
    @RequestMapping("knowledge-edit-sharding-database-chart.html")
    public String knowledgeEditShardingDatabaseChart() {
    	return "/modules/sharding/knowledge_edit_sharding_database_chart";
    }
    // 分库分表新增修改页面
    @RequestMapping("sharding-repository-data-source.html")
    public ModelAndView shardingRepositoryDataSource() {
    	List<Classes> classList = publicService.getClasses();
    	List<ShardingDataSource> shardingDataSourceList = publicService.getShardingDataSource();
    	ModelAndView view = new ModelAndView();
		view.addObject("classList", classList);
		view.addObject("shardingDataSourceList", shardingDataSourceList);
		view.setViewName("/modules/sharding/sharding_repository_data_source");
		return view;
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 数据源新增修改
     */
    @RequestMapping("saveEditDatabase")
	public void saveEditDatabase(HttpServletRequest request,HttpServletResponse response) throws Exception {
		String id = request.getParameter("id");
		String nameZh = request.getParameter("nameZh");
		String nameEn = request.getParameter("nameEn");
		String dbType = request.getParameter("dbType");
		String dbUrl = request.getParameter("dbUrl");
		String dbUser = request.getParameter("dbUser");
		String dbPassword = request.getParameter("dbPassword");
		String status = request.getParameter("status");
		ShardingDataSource shardingDataSource = new ShardingDataSource();
		shardingDataSource.setDbPassword(dbPassword);
		shardingDataSource.setDbType(dbType);
		shardingDataSource.setDbUrl(dbUrl);
		shardingDataSource.setDbUser(dbUser);
		shardingDataSource.setNameEn(nameEn);
		shardingDataSource.setNameZh(nameZh);
		shardingDataSource.setStatus(Integer.parseInt(status));
		List<String> list = new ArrayList<String>();
		String returnData="yes";
		try {
			if("0".equals(id)){
				baseService.saveShardingDataSource(shardingDataSource);
			}else{
				shardingDataSource.setId(Integer.parseInt(id));
				baseService.editShardingDataSource(shardingDataSource);
			}
		} catch (Exception e) {
			returnData = "no";
		}
		list.add(returnData);
		String resultString = formatToJson(list);
		super.pringWriterToPage(resultString, "application/json", response);
	}
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 数据源查询全部
     */
    @RequestMapping("getDatabaseAll")
    public void getDatabaseAll(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	List<ShardingDataSource> shardingDataSourceList = publicService.getShardingDataSource();
    	String resultString = formatToJson(shardingDataSourceList);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 数据源查询单个
     */
    @RequestMapping("findDatabase")
    public void findDatabase(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String id = request.getParameter("id");
    	ShardingDataSource shardingDataSource = publicService.findShardingDataSource(Integer.parseInt(id));
    	String resultString = formatToJson(shardingDataSource);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 数据源删除
     */
    @RequestMapping("delDatabase")
    public void delDatabase(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String id = request.getParameter("id");
    	ShardingDataSource shardingDataSource;
    	List<String> list = new ArrayList<String>();
		String returnData="yes";
		try {
			HashMap<String, Object> queryParams = new HashMap<String, Object>();
			queryParams.put("=,sharding_data_source_id", id);
			long count = publicService.getShardingRepositoryDataSourceCount(queryParams);
			if(0 == count){
				shardingDataSource = publicService.findShardingDataSource(Integer.parseInt(id));
				baseService.deleteShardingDataSource(shardingDataSource);
			}else{
				returnData = "gx";
			}
			
		} catch (Exception e) {
			returnData = "no";
		}
		list.add(returnData);
    	String resultString = formatToJson(list);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 数据配置新增
     */
    @RequestMapping("saveEditshardingRepositoryDataSource")
    public void saveEditshardingRepositoryDataSource(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String id = request.getParameter("id");//ID
    	String classesEn = request.getParameter("classesEn");//知识库
    	String[] value =request.getParameterValues("value");//勾选数据源
    	String openSharding = request.getParameter("openSharding");//是否开启分表
    	String shardingThreshold = request.getParameter("shardingThreshold");//分表记录数阈值
    	String shardingTableCount = request.getParameter("shardingTableCount");//分表个数
    	ShardingRepositoryDataSource shardingRepositoryDataSource = null;
    	String returnData="yes";
    	for (int i = 0; i < value.length; i++) {
    		shardingRepositoryDataSource = new ShardingRepositoryDataSource();
    		shardingRepositoryDataSource.setClassesEn(classesEn);
    		shardingRepositoryDataSource.setShardingDataSourceId(Integer.parseInt(value[i]));
    		shardingRepositoryDataSource.setOpenSharding(Integer.parseInt(openSharding));
    		shardingRepositoryDataSource.setShardingThreshold(Integer.parseInt(shardingThreshold));
    		shardingRepositoryDataSource.setShardingTableCount(Integer.parseInt(shardingTableCount));
    		shardingRepositoryDataSource.setStatus(0);
    		try {
				if("0".equals(id)){
					baseService.saveShardingRepositoryDataSource(shardingRepositoryDataSource);
				}else{
					shardingRepositoryDataSource.setId(Integer.parseInt(id));
					baseService.editShardingRepositoryDataSource(shardingRepositoryDataSource);
				}
			} catch (Exception e) {
				returnData = "no";
			}
    	}
    	List<String> list = new ArrayList<String>();
    	list.add(returnData);
    	String resultString = formatToJson(list);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 数据配置查询全部
     */
    @RequestMapping("getShardingRepositoryDataSource")
    public void getShardingRepositoryDataSource(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String dataSource = request.getParameter("dataSource");//ID
    	List<ShardingRepositoryDataSource> shardingRepositoryDataSourceList = null;
    	HashMap<String, Object> queryClassesParams = null;
    	HashMap<String, Object> queryParams = null;
    	if("0".equals(dataSource)){
    		shardingRepositoryDataSourceList = publicService.getShardingRepositoryDataSource();
    	}else{
    		queryParams = new HashMap<String, Object>();
    		queryParams.put("=,sharding_data_source_id", dataSource);
    		shardingRepositoryDataSourceList = publicService.getShardingRepositoryDataSource(queryParams, 1, publicService.getShardingRepositoryDataSourceCount(queryParams));
    		
    	}
    	List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
    	HashMap<String, Object> data = null;
    	for (int i = 0; i < shardingRepositoryDataSourceList.size(); i++) {
    		ShardingRepositoryDataSource shardingRepositoryDataSource = shardingRepositoryDataSourceList.get(i);
    		data = new HashMap<String, Object>();
    		int openSharding = shardingRepositoryDataSource.getOpenSharding();
    		if(openSharding == 0){
    			data.put("openSharding", "不开启");
    		}else{
    			data.put("openSharding", "开启");
    		}
    		data.put("shardingThreshold", shardingRepositoryDataSource.getShardingThreshold());
    		data.put("shardingTableCount", shardingRepositoryDataSource.getShardingTableCount());
    		data.put("id", shardingRepositoryDataSource.getId());
    		int status = shardingRepositoryDataSource.getStatus();
    		if(status == 0){
    			data.put("status", "未构建");
    		}else{
    			data.put("status", "已构建");
    		}
    		data.put("statusType", status);
    		//classes
    		queryClassesParams = new HashMap<String, Object>();
    		queryClassesParams.put("=,name_en", shardingRepositoryDataSource.getClassesEn());
    		List<Classes> classesList = publicService.getClasses(queryClassesParams, 1, 1);
    		if( null != classesList && classesList.size()>0){
    			for (int j = 0; j < classesList.size(); j++) {
        			Classes classes = classesList.get(j);
        			data.put("calssesName", classes.getNameZh()+"（"+classes.getNameEn()+"）");
        			data.put("calssesNameEn", classes.getNameEn());
    			}
    		}
    		//shardingDataSource
    		ShardingDataSource shardingDataSource = publicService.findShardingDataSource(shardingRepositoryDataSource.getShardingDataSourceId());
    		data.put("shardingDataSourceName",  shardingDataSource.getNameZh()+"（"+shardingDataSource.getNameEn()+"）");
    		data.put("dataSourceId", shardingDataSource.getId());
    		list.add(data);
    	}
    	String resultString = formatToJson(list);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 数据配置查询单个
     */
    @RequestMapping("findShardingRepositoryDataSource")
    public void findShardingRepositoryDataSource(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String id = request.getParameter("id");//ID
    	ShardingRepositoryDataSource shardingRepositoryDataSource = publicService.findShardingRepositoryDataSource(Integer.parseInt(id));
    	String resultString = formatToJson(shardingRepositoryDataSource);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 构建
     */
    @RequestMapping("structureChart")
    public void structureChart(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String id = request.getParameter("id");//ID
    	String classesNameEn = request.getParameter("classesNameEn");//类
    	String returnData="yes";
    	try {
			dynamicService.buildShardingRepositoryTable(classesNameEn, id);
		} catch (Exception e) {
			returnData="no";
		}
    	if("yes".equals(returnData)){
    		ShardingRepositoryDataSource shardingRepositoryDataSource = publicService.findShardingRepositoryDataSource(Integer.parseInt(id));
    		shardingRepositoryDataSource.setStatus(1);
    		baseService.editShardingRepositoryDataSource(shardingRepositoryDataSource);
    	}
    	List<String> list = new ArrayList<String>();
    	list.add(returnData);
    	String resultString = formatToJson(list);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 删除数据源配置
     */
    @RequestMapping("deleteShardingRepositoryDataSource")
    public void deleteShardingRepositoryDataSource(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String id = request.getParameter("id");//ID
    	String classesNameEn = request.getParameter("classesNameEn");//类
    	String returnData="yes";
    	try {
    		dynamicService.deleteShardingRepositoryTable(classesNameEn, id);
    	} catch (Exception e) {
    		returnData="no";
    	}
    	if("yes".equals(returnData)){
    		ShardingRepositoryDataSource shardingRepositoryDataSource = publicService.findShardingRepositoryDataSource(Integer.parseInt(id));
    		baseService.deleteShardingRepositoryDataSource(shardingRepositoryDataSource);
    	}
    	List<String> list = new ArrayList<String>();
    	list.add(returnData);
    	String resultString = formatToJson(list);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 查询全部表
     */
    @RequestMapping("getDatabaseChartAll")
    public void getDatabaseChartAll(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String structureChartId = request.getParameter("structureChartId");//ID
    	String structureChartClassesEn = request.getParameter("structureChartClassesEn");//类
    	HashMap<String, Object> queryParams = new HashMap<String, Object>();
    	queryParams.put("=,classes_en", structureChartClassesEn);
    	queryParams.put("=,sharding_data_source_id", structureChartId);
    	
    	List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
    	HashMap<String, Object> data = null;
    	HashMap<String, Object> queryClassesParams = null;
    	List<ShardingRepositoryCount> shardingRepositoryCountLsit = publicService.getShardingRepositoryCount(queryParams, 1, publicService.getShardingRepositoryCountCount(queryParams));
    	for (int i = 0; i < shardingRepositoryCountLsit.size(); i++) {
    		ShardingRepositoryCount shardingRepositoryCount = shardingRepositoryCountLsit.get(i);
    		data = new HashMap<String, Object>();
    		data.put("dataCount", shardingRepositoryCount.getDataCount());
    		data.put("tableName", shardingRepositoryCount.getTableName());
    		if(null != shardingRepositoryCount.getUpdateTime() && !"".equals(shardingRepositoryCount.getUpdateTime())){
    			data.put("updateTime", shardingRepositoryCount.getUpdateTime());
    		}else{
    			data.put("updateTime", "");
    		}
    		//classes
    		queryClassesParams = new HashMap<String, Object>();
    		queryClassesParams.put("=,name_en", shardingRepositoryCount.getClassesEn());
    		List<Classes> classesList = publicService.getClasses(queryClassesParams, 1, 1);
    		if( null != classesList && classesList.size()>0){
    			for (int j = 0; j < classesList.size(); j++) {
        			Classes classes = classesList.get(j);
        			data.put("calssesName", classes.getNameZh()+"（"+classes.getNameEn()+"）");
        			data.put("calssesNameEn", classes.getNameEn());
    			}
    		}
    		//shardingDataSource
    		ShardingDataSource shardingDataSource = publicService.findShardingDataSource(shardingRepositoryCount.getShardingDataSourceId());
    		data.put("shardingDataSourceName",  shardingDataSource.getNameZh()+"（"+shardingDataSource.getNameEn()+"）");
    		list.add(data);
    	}
    	
    	String resultString = formatToJson(list);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
    /**
     * @author zf
     * @param request
     * @param response
     * @throws Exception
     * 2018年2月2日15:20:04
     * 测试连接
     */
    @RequestMapping("testConnection")
    public void testConnection(HttpServletRequest request,HttpServletResponse response) throws Exception {
    	String id = request.getParameter("id");//ID
    	ShardingDataSource shardingDataSource = publicService.findShardingDataSource(Integer.parseInt(id));
    	String driver= "";
    	String returnData= "yes";
    	if("mysql".equals(shardingDataSource.getDbType())){
    		driver="com.mysql.jdbc.Driver";  //获取mysql数据库的驱动类
    	}
        String url=shardingDataSource.getDbUrl(); //连接数据库（kucun是数据库名）
        String name=shardingDataSource.getDbUser();//连接mysql的用户名
        String pwd=shardingDataSource.getDbPassword();//连接mysql的密码
        Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,name,pwd);
		} catch (Exception e1) {
			returnData = "no";
		}
        try{
	        if(conn!=null){
	          conn.close();
	        }
	     }catch(SQLException e){
	        e.printStackTrace();
	     }
    	List<String> list = new ArrayList<String>();
    	list.add(returnData);
    	String resultString = formatToJson(list);
    	super.pringWriterToPage(resultString, "application/json", response);
    }
}
